﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.CodeDom.Compiler;
using System.CodeDom;
using System.Threading;

namespace Btrieve_Retriever
{

    /*
     * This class is in charge of retrieving the details of a certain order from the database
     * and displaying it to the user in a similar fashion to the Niese output.
     * */
    public partial class OrderDescrip : Form
    {
        public string orderNumber;
        DoorDetails door;
        // The number of the field in the MASKENCODE table containing the data for this property.
        string fabreNr = "'35'", modellTypNr = "'09'", ausfuNr = "'16'", anschlagNr = "'08'",
            aufgehrNr = "'12'", schlossnormNr = "'11'", verrNr = "'13'", gitterTypNr = "'22'",
            gitterFarbeNr = "'03'", sockelTypNr = "'03'";
        String glassInfo;

        //Padding is used to align printouts to columns
        int printPaddingText = 40;
        int printPaddingNumbers = 10;
        Boolean closeDescription = false;

        public OrderDescrip(String orderNumber)
        {
            InitializeComponent();
            this.orderNumber = orderNumber;
            retrieveOrderWorker.RunWorkerAsync();
        }

        private void retrieveOrderWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            try
            {
                fillDescription();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error in retrieving order information. \n\n\n Details: " + ex.Message);
            }
        }

        private void retrieveOrderWorker_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            richTextBox1.AppendText(e.UserState.ToString());
            richTextBox1.Refresh();
        }
        /*
         * This function runs an initial query on the AUKUN1 table to get the code value for 
         * the different door properties. These codes are stored in the door object. A number
         * of queries are then executed to get the real value of the code using the MASKENCODE table.
         * */
        private void fillDescription()
        {
            string query = String.Format("SELECT htmodbez, eklasse, htobfla, modelltyp, ausfuehr, anstyp, schiek, aufricht, schlotyp, verriegel,"
            + "htgitter, filler5, fakttab, aufnr2, htobflin, htfuin, htfuau FROM AUKUN1 WHERE aufnr1 = {0}", orderNumber);
            SqlCommand cmd = new SqlCommand(query, Program.myConnection2);
            var dr = cmd.ExecuteReader();

            List<string> list = new List<string>();
            if (dr.HasRows)
            {
                door = new DoorDetails();
                while (dr.Read())
                {
                    door.auftragNr = orderNumber;
                    door.serienNr = dr[13].ToString().Trim();
                    door.modell = dr[0].ToString().Trim();
                    door.klasse = dr[1].ToString().Trim();
                    
                    door.furnierInnen = dr[15].ToString().Trim();
                    if (door.furnierInnen.Equals("NIX"))
                        door.furnierInnen = "Topicore";
                    door.furnierAussen = dr[16].ToString().Trim();
                    if (door.furnierAussen.Equals("NIX"))
                        door.furnierAussen = "Topicore";
                    
                    door.farbeAussen = "'" + dr[2].ToString() + "'";
                    door.modellTyp = "'" + dr[3].ToString() + "'";
                    door.ausFuhrung = "'" + dr[4].ToString() + "'";
                    door.anschlagTyp = "'" + dr[5].ToString() + "'";
                    door.schienenkurzung = dr[6].ToString().Trim();
                    door.aufGehrichtung = "'" + dr[7].ToString() + "'";
                    door.schlossNorm = "'" + dr[8].ToString() + "'";
                    door.verriegelung = "'" + dr[9].ToString() + "'";
                    door.gitterTyp = "'" + dr[10].ToString() + "'";
                    door.farbeInnen = "'" + dr[14].ToString() + "'";
                    
                    char[] charSeparators = new char[] { ' ' };
                    String temp = System.Text.RegularExpressions.Regex.Replace(dr[12].ToString(), @"\s+", " ");
                    String[] dimensionInfo = temp.Split(charSeparators, 6);

                    char filler = dr[11].ToString().ElementAt(dr[11].ToString().Length - 1);
                    door.height = filler + "" +  dimensionInfo[0];
                    door.width = dimensionInfo[1];
                    door.stockfriesBreiteSchloss = dimensionInfo[2];
                    door.stockfriesBreiteBandseitig = dimensionInfo[3];
                    door.stockQuarsfriesBreite = dimensionInfo[4];
                }
                
                cmd.Dispose(); dr.Close();

                // Certain field information is in the TTSTDAT table
                String queryN = String.Format("SELECT datstring, typ, stuliart, lange, breite, furn1, furn2, gmatar FROM TTSTDAT WHERE aufnr1 = {0} and (stuliart = {1} or stuliart = {2} or stuliart = {3})", orderNumber, "'GIT'", "'SO'", "'HT'");
                SqlCommand cmdN = new SqlCommand(queryN, Program.myConnection2);
                SqlDataReader drN = cmdN.ExecuteReader();
                
                if (drN.HasRows)
                {
                    while (drN.Read())
                    {
                        if (drN[2].ToString().Trim().Equals("SO"))
                        {
                            String sockT = drN[1].ToString().Trim();
                            if (sockT.Length == 3)
                            {
                                sockT = sockT.Substring(1);
                            }
                            door.sockelTyp = "'" + sockT + "'";
                        }
                        if (drN[2].ToString().Trim().Equals("GIT"))
                        {
                            string temp = drN[0].ToString().Split(' ')[0];
                            door.gitterFarbe = "'" + temp.Substring(2) + "'";
                        }

                        if (drN[2].ToString().Trim().Equals("HT"))
                        {
                            door.turblattHohe = drN[3].ToString().Trim();
                            door.turblattBreite = drN[4].ToString().Trim();

                            if(door.furnierAussen.Equals(drN[5].ToString().Trim()))
                            {
                                door.furnierAussen = drN[7].ToString().Trim();
                            }

                            if (door.furnierInnen.Equals(drN[6].ToString().Trim()))
                            {
                                door.furnierInnen = drN[7].ToString().Trim();
                            }
                        }
                    }
                }
                cmdN.Dispose(); drN.Close();

                // A large query that returns all corresponding data to the AUKUN1 codes from the MASKENCODE table
                string completeQuery = String.Format("SELECT bezeichnung, feldnr, maske, code FROM MASKENCODE WHERE (maske = {0} and ((feldnr = {1} and code = {2}) or (feldnr = {3} and code = {4}) or (feldnr = {5} and code = {6}) or " +
                "(feldnr = {7} and code = {8}) or (feldnr = {9} and code = {10}) or (feldnr = {11} and code = {12}) or " +
                "(feldnr = {13} and code = {14}) or (feldnr = {15} and code = {16}) or (feldnr = {17} and code = {18}))) or " +
                "(maske = {19} and feldnr = {20} and code = {21}) or (maske = {22} and feldnr = {23} and code = {24})",
                "'AUF'", fabreNr, door.farbeAussen,
                modellTypNr, door.modellTyp,
                ausfuNr, door.ausFuhrung,
                anschlagNr, door.anschlagTyp,
                aufgehrNr, door.aufGehrichtung,
                schlossnormNr, door.schlossNorm,
                verrNr, door.verriegelung,
                gitterTypNr, door.gitterTyp,
                fabreNr, door.farbeInnen,
                "'GIT'", gitterFarbeNr, door.gitterFarbe,
                "'SO'", sockelTypNr, door.sockelTyp);

                SqlCommand command = new SqlCommand(completeQuery, Program.myConnection2);
                using (var dataReader = command.ExecuteReader())
                {
                    if (dataReader.HasRows)
                    {
                        while (dataReader.Read())
                        {
                            if (dataReader[2].ToString().Trim().Equals("AUF"))
                            {
                                if (("'" + dataReader[1].ToString().Trim() + "'").Equals(fabreNr))
                                {
                                    if (door.farbeAussen.Equals(("'" + dataReader[3].ToString() + "'")))
                                    {
                                        door.farbeAussen = dataReader[0].ToString().Trim();
                                    }
                                    if (door.farbeInnen.Equals(("'" + dataReader[3].ToString() + "'")))
                                    {
                                        door.farbeInnen = dataReader[0].ToString().Trim();
                                    }
                                }
                                if (("'" + dataReader[1].ToString().Trim() + "'").Equals(modellTypNr))
                                {
                                    door.modellTyp = dataReader[0].ToString().Trim();
                                }
                                if (("'" + dataReader[1].ToString().Trim() + "'").Equals(aufgehrNr))
                                {
                                    door.aufGehrichtung = dataReader[0].ToString().Trim();
                                }
                                if (("'" + dataReader[1].ToString().Trim() + "'").Equals(ausfuNr))
                                {
                                    door.ausFuhrung = dataReader[0].ToString().Trim();
                                }
                                if (("'" + dataReader[1].ToString().Trim() + "'").Equals(anschlagNr))
                                {
                                    door.anschlagTyp = dataReader[0].ToString().Trim();
                                }
                                if (("'" + dataReader[1].ToString().Trim() + "'").Equals(schlossnormNr))
                                {
                                    door.schlossNorm = dataReader[0].ToString().Trim();
                                }
                                if (("'" + dataReader[1].ToString().Trim() + "'").Equals(verrNr))
                                {
                                    door.verriegelung = dataReader[0].ToString().Trim();
                                }
                                if (("'" + dataReader[1].ToString().Trim() + "'").Equals(gitterTypNr))
                                {
                                    door.gitterTyp = dataReader[0].ToString().Trim();
                                }
                            }
                            if (dataReader[2].ToString().Trim().Equals("GIT"))
                            {
                                if (("'" + dataReader[1].ToString().Trim() + "'").Equals(gitterFarbeNr))
                                {
                                    door.gitterFarbe = dataReader[0].ToString().Trim();
                                }
                            }
                            if (dataReader[2].ToString().Trim().Equals("SO"))
                            {
                                if (("'" + dataReader[1].ToString().Trim() + "'").Equals(sockelTypNr))
                                {
                                    door.sockelTyp = dataReader[0].ToString().Trim();
                                }
                            }
                        }
                    }
                    command.Dispose(); dataReader.Close();
                }
                printDescription();
            }
            else
            {
                cmd.Dispose(); dr.Close();
                MessageBox.Show("ORDER NOT FOUND");
                closeDescription = true;
            }
        }

        /*
         * Create a stringbuilder and fill it with text for the different door properties.
         * This function combines the door property groups as in the Niese output.
         * */
        public void printDescription()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Serien-NR".PadRight(printPaddingText) + door.serienNr.PadRight(printPaddingText));
            sb.Append(Environment.NewLine + "Auftrags-NR".PadRight(printPaddingText) + door.auftragNr.PadRight(printPaddingText));
            sb.Append(Environment.NewLine + "Modell".PadRight(printPaddingText) + door.modell.PadRight(printPaddingText));
            sb.Append(Environment.NewLine + "Klasse".PadRight(printPaddingText) + door.klasse.PadRight(printPaddingText));

            sb.Append(Environment.NewLine + Environment.NewLine);
            sb.Append("------------------------------------------------------------------");
            sb.Append(Environment.NewLine + "------------------------------------------------------------------");
            sb.Append(Environment.NewLine);

            sb.Append(Environment.NewLine + "Furnier Aussen".PadRight(printPaddingText) + door.furnierAussen.PadRight(printPaddingText));
            sb.Append(Environment.NewLine + "Furnier Innen".PadRight(printPaddingText) + door.furnierInnen.PadRight(printPaddingText));
            sb.Append(Environment.NewLine + "Farbe Aussen".PadRight(printPaddingText) + door.farbeAussen.PadRight(printPaddingText));
            sb.Append(Environment.NewLine + "Farbe Innen".PadRight(printPaddingText) + door.farbeInnen.PadRight(printPaddingText));
            sb.Append(Environment.NewLine + "Modelltyp".PadRight(printPaddingText) + door.modellTyp.PadRight(printPaddingText));
            sb.Append(Environment.NewLine + "Ausfuhrung".PadRight(printPaddingText) + door.ausFuhrung.PadRight(printPaddingText));
            sb.Append(Environment.NewLine + "Stock Hohe".PadRight(printPaddingText) + door.height.PadRight(printPaddingText));
            sb.Append(Environment.NewLine + "Stock Breite".PadRight(printPaddingText) + door.width.PadRight(printPaddingText));
            sb.Append(Environment.NewLine + "Stock Quersfries Breite".PadRight(printPaddingText) + door.stockQuarsfriesBreite.PadRight(printPaddingText));
            sb.Append(Environment.NewLine + "Stockfries Breite Schlosseitig".PadRight(printPaddingText) + door.stockfriesBreiteSchloss.PadRight(printPaddingText));
            sb.Append(Environment.NewLine + "Stockfries Breite Bandseitig".PadRight(printPaddingText) + door.stockfriesBreiteBandseitig.PadRight(printPaddingText));
            sb.Append(Environment.NewLine + "Turblatthohe".PadRight(printPaddingText) + door.turblattHohe.PadRight(printPaddingText));
            sb.Append(Environment.NewLine + "Turblattbreite".PadRight(printPaddingText) + door.turblattBreite.PadRight(printPaddingText));
            sb.Append(Environment.NewLine + "Anschlagtyp".PadRight(printPaddingText) + door.anschlagTyp.PadRight(printPaddingText));
            sb.Append(Environment.NewLine + "Schienenkurzung".PadRight(printPaddingText) + door.schienenkurzung.PadRight(printPaddingText));
            sb.Append(Environment.NewLine + "Aufgehrichtung".PadRight(printPaddingText) + door.aufGehrichtung.PadRight(printPaddingText));
            sb.Append(Environment.NewLine + "Schlossnorm".PadRight(printPaddingText) + door.schlossNorm.PadRight(printPaddingText));
            sb.Append(Environment.NewLine + "Verriegelung".PadRight(printPaddingText) + door.verriegelung.PadRight(printPaddingText));
            
            if (!door.gitterTyp.Trim().Equals("'NA'") && !door.gitterFarbe.Trim().Equals("'NA'"))
            {
                sb.Append(Environment.NewLine + "Gittertyp".PadRight(printPaddingText) + door.gitterTyp.PadRight(printPaddingText));
                sb.Append(Environment.NewLine + "Gitterfarbe".PadRight(printPaddingText) + door.gitterFarbe.PadRight(printPaddingText));
            }
            
            if (!door.sockelTyp.Equals("'NA'"))
            {
                sb.Append(Environment.NewLine + "Sockeltyp".PadRight(printPaddingText) + door.sockelTyp.PadRight(printPaddingText));
            }

            sb.Append(Environment.NewLine + Environment.NewLine);
            sb.Append("------------------------------------------------------------------");
            sb.Append(Environment.NewLine + "------------------------------------------------------------------");
            sb.Append(Environment.NewLine);

            //Show the information we have so far to the user
            retrieveOrderWorker.ReportProgress(0, sb.ToString());
            sb.Clear();

            //Query to get the additional text information from table TAUKU2
            string query2; SqlCommand cmd2; SqlDataReader dr2;
            query2 = String.Format("SELECT * FROM TAUKU2 where aufnr1 = {0}", orderNumber);
            cmd2 = new SqlCommand(query2, Program.myConnection2);
            dr2 = cmd2.ExecuteReader();

            if (dr2.HasRows)
            {
                dr2.Read();
                sb.Append("ZUSATZTEXT" + Environment.NewLine);
                for (int i = 2; i < 22; i++)
                {
                    if (!dr2.IsDBNull(i))
                    {
                        if (dr2[i].ToString().Trim().Length > 1)
                        {
                            sb.AppendLine(dr2[i].ToString().Trim());
                        }
                    }
                }

                sb.Append(Environment.NewLine + "------------------------------------------------------------------");
                sb.Append(Environment.NewLine + "------------------------------------------------------------------");
                retrieveOrderWorker.ReportProgress(0, sb.ToString());
                sb.Clear();
                cmd2.Dispose(); dr2.Close();
            }
            
            string query; SqlCommand cmd; SqlDataReader dr;
            query = String.Format("SELECT prodart, artbez, untstuklnr, lange, breite, dicke, menge, gmatar, gmatbez FROM TTMPOS WHERE aufnr1 = {0} ORDER BY untstuklnr", orderNumber);
            cmd = new SqlCommand(query, Program.myConnection2);
            dr = cmd.ExecuteReader();

            //Set the group to a value that will not occur in the data we have
            string teil = "not";
            sb.Clear();
            sb = new StringBuilder();
            sb.Append(Environment.NewLine + "BESCHLAGSDATEN" + Environment.NewLine + Environment.NewLine);

            while (dr.Read())
            {
                //All records corresponding to BESCHLAG are added
                if (dr[0].ToString().Trim().Equals("BESCHLAG"))
                {
                    sb.Append(dr[7].ToString().Trim().TrimStart('0').PadRight(printPaddingText) + dr[8].ToString().Trim().TrimStart('0').PadRight(printPaddingText) + dr[6].ToString().Trim().TrimStart('0').PadRight(printPaddingNumbers) + Environment.NewLine);
                }
                else
                {
                    //Records that belong to a group we are already printing
                    if (dr[2].ToString().Equals(teil))
                    {
                        sb.Append(Environment.NewLine + dr[0].ToString().Trim().TrimStart('0').PadRight(printPaddingText) + dr[1].ToString().Trim().TrimStart('0').PadRight(printPaddingText) + dr[3].ToString().Trim().TrimStart('0').PadRight(printPaddingNumbers) + dr[4].ToString().Trim().TrimStart('0').PadRight(printPaddingNumbers) + dr[5].ToString().Trim().TrimStart('0').PadRight(printPaddingNumbers) + dr[6].ToString().TrimStart('0').PadRight(printPaddingNumbers));
                    }
                    else
                    {
                        retrieveOrderWorker.ReportProgress(0, sb.ToString());
                        sb.Clear();

                        sb.Append(Environment.NewLine + Environment.NewLine);
                        sb.Append("------------------------------------------------------------------");
                        sb.Append(Environment.NewLine + "------------------------------------------------------------------");
                        sb.Append(Environment.NewLine);
                        teil = dr[2].ToString();

                        Program.myConnection3.Close();
                        Program.myConnection3.Open();

                        cmd.Dispose();
                        String queryN = String.Format("SELECT stklbez, lange, breite, dicke, datstring FROM TTSTDAT WHERE aufnr1 = {0} and stklkbez = {1}", orderNumber, ("'" + teil.Trim() + "'"));
                        SqlCommand cmdN = new SqlCommand(queryN, Program.myConnection3);
                        // cmd.CommandText = queryN;
                        SqlDataReader drN = cmdN.ExecuteReader();

                        if (drN.HasRows)
                        {
                            drN.Read();
                            sb.Append(Environment.NewLine + "Teilgruppe:".PadRight(printPaddingText) + drN[0].ToString().Trim().Replace("\0", "").PadRight(printPaddingText));
                            sb.Append("Lange".PadRight(printPaddingNumbers) + "Breite".PadRight(printPaddingNumbers) + "Dicke".PadRight(printPaddingNumbers) + "Stuck".PadRight(printPaddingNumbers));
                            sb.Append(Environment.NewLine + "".PadRight(printPaddingText) + "".PadRight(printPaddingText));
                            for (int i = 0; i < 4; i++)
                            {
                                if (!drN.IsDBNull(i))
                                {
                                    String temp = drN[i].ToString().Trim().Replace("\0", "");
                                    if (!(String.IsNullOrWhiteSpace(temp) || String.IsNullOrEmpty(temp)) && i != 0)
                                    {
                                        sb.Append(temp.TrimStart('0').PadRight(printPaddingNumbers));
                                    }
                                }
                                else
                                {
                                    Console.WriteLine("NULL ENTRY");
                                }


                            }
                            if (teil.Contains("GLAS"))
                            {
                                glassInfo = drN[4].ToString().Trim();
                               sb.Append(Environment.NewLine + "Aussen:".PadRight(printPaddingText));
                                int level = 0;
                                while (glassInfo.Length > 0)
                                {
                                    if (!char.IsWhiteSpace(glassInfo.ElementAt(0)))
                                    {
                                        sb.Append(glassInfo.ElementAt(0));
                                        glassInfo = glassInfo.Substring(1);
                                    }
                                    else
                                    {
                                        switch (numberOfSpaces(ref glassInfo))
                                        {
                                            case 0:
                                                {
                                                    sb.Append(" ");
                                                    break;
                                                }
                                            case 1:
                                                {
                                                    sb.Append("\t");
                                                    break;
                                                }
                                            case 2:
                                                {
                                                    sb.Append("\n");
                                                    level++;
                                                    if (level == 1)
                                                        sb.Append("Mitte:".PadRight(printPaddingText));
                                                    if (level == 2)
                                                        sb.Append("Innen:".PadRight(printPaddingText));
                                                    break;
                                                }
                                            default:
                                                {
                                                    sb.Append(" ER ");
                                                    break;
                                                }
                                        }
                                    }
                                }
                            }
                        }
                        cmdN.Dispose(); drN.Close();
                        sb.Append(Environment.NewLine + dr[0].ToString().Trim().TrimStart('0').PadRight(printPaddingText) + dr[1].ToString().Trim().TrimStart('0').PadRight(printPaddingText) + dr[3].ToString().Trim().TrimStart('0').PadRight(printPaddingNumbers) + dr[4].ToString().Trim().TrimStart('0').PadRight(printPaddingNumbers) + dr[5].ToString().Trim().TrimStart('0').PadRight(printPaddingNumbers) + dr[6].ToString().TrimStart('0').PadRight(printPaddingNumbers));
                    }
                }

            }
            dr.Close();
            retrieveOrderWorker.ReportProgress(0, sb.ToString());
            //richTextBox1.AppendText(sb.ToString());
            //  richTextBox1.Text = sb.ToString();

        }

        private void richTextBox1_TextChanged(object sender, EventArgs e)
        {

        }

        /*
         * function to count the number of conseeutive whitespaces.
         * Used to split the string containing the glass information.
         * */
        private int numberOfSpaces(ref String str)
        {
            int numOfSpaces = 0;
            while (str.Length > 0)
            {
                if (char.IsWhiteSpace(str.ElementAt(0)))
                {
                    numOfSpaces++;
                    str = str.Substring(1);
                }
                else
                {
                    break;
                }
            }
            if (numOfSpaces == 1)
                return 0;
            if (numOfSpaces > 1 && numOfSpaces < 20)
            {
                return 1;
            }
            else
            {
                return 2;
            }
      
        }
      
        /*
         * Function that returns a string with all special escape characters shown
         * */
        private static string ToLiteral(string input)
        {
            using (var writer = new StringWriter())
            {
                using (var provider = CodeDomProvider.CreateProvider("CSharp"))
                {
                    provider.GenerateCodeFromExpression(new CodePrimitiveExpression(input), writer, null);
                    return writer.ToString();
                }
            }
        }

        private void retrieveOrderWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            label1.Visible = false;
            if (closeDescription)
            {
                this.Close();
            }
        }
    }
}
